package com.smart.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.smart.pojo.SalesOrder;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.smart.resp.EchartsData;
import com.smart.resp.PopularProduct;
import com.smart.resp.SalesOrderResp;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author lyj
 * @since 2023-05-15
 */
public interface SalesOrderMapper extends BaseMapper<SalesOrder> {

    @Select("select so.*,\n" +
            "p.name productName,\n" +
            "u.username username\n" +
            "from sales_order so\n" +
            "left join product p\n" +
            "on so.product_id=p.id\n" +
            "and p.del_flag=0\n" +
            "left join user u\n" +
            "on so.user_id=u.id\n" +
            "and u.del_flag=0 ${ew.customSqlSegment}")
    Page<SalesOrderResp> queryByCondition(@Param("ew") QueryWrapper<SalesOrderResp> wrapper, Page<SalesOrderResp> page);

    @Select("select count(1)\n" +
            "from sales_order\n" +
            "where year(now())=year(create_date)\n" +
            "and month(now())=month(create_date)\n" +
            "and del_flag=0")
    int getCurrentOrders();

    @Select("select ifnull(sum(turnover),0)\n" +
            "from sales_order\n" +
            "where year(now())=year(create_date)\n" +
            "and month(now())=month(create_date)\n" +
            "and del_flag=0\n" +
            "and status=2")
    BigDecimal getCurrentTurnover();

    @Select("select ifnull(sum(turnover),0)\n" +
            "from sales_order\n" +
            "where del_flag=0\n" +
            "and status=2")
    BigDecimal getTotalTurnover();

    @Select("select p.name productName,\n" +
            "sum(so.volume) total\n" +
            "from sales_order so\n" +
            "left join product p\n" +
            "on so.product_id=p.id and p.del_flag=0\n" +
            "where yearweek(now())=yearweek(so.create_date)\n" +
            "and so.del_flag=0\n" +
            "group by so.product_id,p.name\n" +
            "order by total desc\n" +
            "limit 0,1")
    PopularProduct getWeekPopularProduct();

    @Select("select p.name productName,\n" +
            "sum(so.volume) total\n" +
            "from sales_order so\n" +
            "left join product p\n" +
            "on so.product_id=p.id and p.del_flag=0\n" +
            "where year(now())=year(so.create_date)\n" +
            "and so.del_flag=0\n" +
            "group by so.product_id,p.name\n" +
            "order by total desc\n" +
            "limit 0,1")
    PopularProduct getYearPopularProduct();

    @Select("select month(create_date) name,\n" +
            "sum(turnover) value\n" +
            "from sales_order\n" +
            "where status=2\n" +
            "and year(now())=year(create_date)\n" +
            "group by month(create_date)\n" +
            "order by month(create_date)")
    List<EchartsData> getTurnoverData();

    @Select("select p.name name,\n" +
            "ifnull(sum(so.turnover),0) value\n" +
            "from product p\n" +
            "left join sales_order so\n" +
            "on p.id=so.product_id\n" +
            "and so.del_flag=0\n" +
            "and so.status=2\n" +
            "where p.del_flag=0\n" +
            "group by p.id\n" +
            "order by value")
    List<EchartsData> getSalesData();

    @Select("select p.name name,\n" +
            "ifnull(sum(so.turnover - so.volume * cp.production_costs),0) value\n" +
            "from product p\n" +
            "left join sales_order so\n" +
            "on p.id=so.product_id\n" +
            "and so.del_flag=0\n" +
            "and so.status=2\n" +
            "left join conceptual_product cp\n" +
            "on p.conceptual_id=cp.id\n" +
            "and cp.del_flag=0\n" +
            "where p.del_flag=0\n" +
            "group by p.id\n" +
            "order by value")
    List<EchartsData> getProfitData();
}
